package edu.sdjzu.exam.mapper.exam;

import edu.sdjzu.exam.domain.exam.StudentGrade;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Mapper
public interface StudentGradesMapper {

    @Insert("INSERT INTO student_grades (exam_id, user_id, score, comments, dispute_status, dispute_comments) " +
            "VALUES (#{examId}, #{userId}, #{score}, #{comments}, #{disputeStatus}, #{disputeComments})")
    @Options(useGeneratedKeys = true, keyProperty = "gradeId")
    void insertStudentGrade(StudentGrade studentGrade);

    @Select("SELECT * FROM student_grades WHERE exam_id = #{examId} AND user_id = #{userId}")
    StudentGrade selectGradeByExamAndStudent(@Param("examId") Long examId, @Param("userId") Long userId);

    @Update("UPDATE student_grades SET score = #{score}, comments = #{comments}, " +
            "dispute_status = #{disputeStatus}, dispute_comments = #{disputeComments}, updated_at = NOW() " +
            "WHERE exam_id = #{examId} AND user_id = #{userId}")
    void updateStudentGrade(@Param("examId") Long examId,
                           @Param("userId") Long userId,
                           @Param("score") BigDecimal score,
                           @Param("comments") String comments,
                           @Param("disputeStatus") String disputeStatus,
                           @Param("disputeComments") String disputeComments);

    @Update("UPDATE student_grades SET score = #{score}, comments = #{comments}, " +
            "dispute_status = #{disputeStatus}, dispute_comments = #{disputeComments}, updated_at = NOW() " +
            "WHERE grade_id = #{gradeId}")
    void updateStudentGradeById(StudentGrade studentGrade);

    @Select("SELECT * FROM student_grades WHERE exam_id = #{examId}")
    List<StudentGrade> selectGradesByExamId(Long examId);

    @Select("SELECT COUNT(*) FROM student_grades WHERE exam_id = #{examId} AND dispute_status = #{disputeStatus}")
    int countGradesByExamAndStatus(@Param("examId") Long examId, @Param("disputeStatus") String disputeStatus);

    @Select("SELECT DISTINCT e.exam_id, e.title as exam_title, " +
            "COUNT(DISTINCT es.student_id) as total_students, " +
            "COUNT(DISTINCT CASE WHEN sg.dispute_status = 'pending' OR sg.dispute_status = 'in_progress' THEN es.student_id END) as pending_students, " +
            "COUNT(DISTINCT CASE WHEN sg.dispute_status = 'completed' THEN es.student_id END) as completed_students " +
            "FROM exams e " +
            "JOIN exam_sessions es ON e.exam_id = es.exam_id " +
            "LEFT JOIN student_grades sg ON e.exam_id = sg.exam_id AND es.student_id = sg.user_id " +
            "WHERE es.status = 'submitted' " +
            "GROUP BY e.exam_id, e.title " +
            "HAVING pending_students > 0 " +
            "ORDER BY pending_students DESC")
    List<Map<String, Object>> selectExamsNeedingGrading();

    @Select("SELECT es.student_id, u.realname as student_name, " +
            "COALESCE(sg.score, 0) as current_score, " +
            "CASE WHEN sg.dispute_status IS NULL OR sg.dispute_status = 'pending' OR sg.dispute_status = 'in_progress' THEN 'pending' ELSE 'completed' END as grade_status, " +
            "es.end_time as submit_time " +
            "FROM exam_sessions es " +
            "LEFT JOIN student_grades sg ON es.exam_id = sg.exam_id AND es.student_id = sg.user_id " +
            "LEFT JOIN users u ON es.student_id = u.user_id " +
            "WHERE es.exam_id = #{examId} AND es.status = 'submitted' " +
            "ORDER BY grade_status ASC, es.end_time ASC")
    List<Map<String, Object>> selectGradesWithStudentNamesByExamId(Long examId);
    
    @Select("SELECT es.student_id, u.realname as student_name, e.title as exam_title, " +
            "es.exam_id, COALESCE(sg.score, 0) as total_score, " +
            "CASE WHEN sg.dispute_status = 'pending' OR sg.dispute_status IS NULL THEN 'pending' ELSE 'completed' END as grade_status, " +
            "sg.created_at as graded_at " +
            "FROM exam_sessions es " +
            "LEFT JOIN student_grades sg ON es.exam_id = sg.exam_id AND es.student_id = sg.user_id " +
            "LEFT JOIN users u ON es.student_id = u.user_id " +
            "LEFT JOIN exams e ON es.exam_id = e.exam_id " +
            "WHERE es.exam_id = #{examId} AND es.status = 'submitted' " +
            "ORDER BY CASE WHEN sg.dispute_status = 'pending' OR sg.dispute_status = 'in_progress' THEN 1 ELSE 0 END, sg.score DESC, es.student_id ASC")
    List<Map<String, Object>> selectDetailedGradesByExamId(Long examId);
    
    @Select("SELECT e.exam_id, e.title, e.start_time " +
            "FROM exams e " +
            "WHERE e.exam_id = #{examId}")
    Map<String, Object> selectExamBasicInfo(Long examId);

    @Select("SELECT * FROM student_grades WHERE user_id = #{studentId}")
    List<StudentGrade> selectGradesByStudentId(Long studentId);
}